/*
 * Copyright 2007-208 the original author or authors.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */ 
 
package com.cassevern.grails.util

class Build400GrailsDomain { 
	List attrs = []; // holds TableAttributes
	def index; // holds array of index columns
	def ip, user, password, library, file, domainName, verbose
	private def RRN_SUFFIX = '_RRN' 
	boolean useRRNasKey = false
	def fieldNameConvList = [ // defaults:
	                          [from:'%', to:'Pct'],
	                          [from:'#', to:'No'],
	                          [from:'\'', to:''],
	                          [from:'\\?', to:'Question'],
	                          [from:'-', to:'_'],
	                          [from:'=', to:'_'],
	                          [from:'/', to:'_'],
	                          [from:',', to:''],
	                          [from:'\\.', to:'']
		                    ]
	
	void runRRNBuilder (def fieldNameConvListIn) {
		if (!ip || !user || !password || !library || !file || !domainName) { 
			println "ERROR: Please set ip, user, password, library, file, and domainName on the ${this.class} constructor "
			return
		}
		useRRNasKey = true
		if (fieldNameConvListIn) {
			fieldNameConvList  = fieldNameConvListIn
		}
		retrieveTableAttributes() 		
		writeHibernateXML()
		createStoredProcedure()
		createRRNView()
		createDomainClass()
		writeHibernateConfigXML()
	}

	void runDSLMappingBuilder (def fieldNameConvListIn) {
		if (fieldNameConvListIn) {
			fieldNameConvList  = fieldNameConvListIn
		}
		retrieveTableAttributes() 	
		retrieveIndex () 
		createDomainClass()
	}
	
	void retrieveTableAttributes () {
		def attrNames = [:]
		def rs = groovy.sql.Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver").
						getConnection().getMetaData().getColumns(null, library, file, null)
		while (rs.next()) { 
			def attr = new TableAttributes()
			attr.colName = rs.getString("COLUMN_NAME")
			attr.colTypeName = rs.getString("TYPE_NAME")
			attr.size = rs.getInt("COLUMN_SIZE")
			attr.scale = rs.getInt("DECIMAL_DIGITS")
			attr.remarks = rs.getString("REMARKS")
			attr.nullable = rs.getString("IS_NULLABLE") == 'NO'  

			if (attr.colName == attr.remarks || !attr.remarks) {
				attr.name = attr.colName.toLowerCase()
			} else { // build attribute name with remarks field
				attr.name = ''
				// make the 1st letter of each word uppercase then lowercase the remaining letters  
				new StringTokenizer (attr.remarks, ' ').each {  
					attr.name <<= it.getAt(0).toUpperCase()+it.substring(1).toLowerCase()
				}
				fieldNameConvList.each {fieldNameConv ->
					attr.name = attr.name.replaceAll(fieldNameConv.from, fieldNameConv.to)
				}
				attr.name = attr.name.replaceAll(/^\w/) {it[0].toLowerCase()} // lowercase 1st letter
			}
			if (!isValidVariableName(attr.name)) {
				attr.name = attr.colName.toLowerCase()
			}
			if (attr.name.matches(/.*#.*/)) {
				attr.name = attr.name.replaceAll(/#/, 'No')
			}
			if (attr.colName.matches(/.*#.*/)) {
				//throw new RuntimeException("Grails/Hibernate does not support field names that contain a pound symbol (#)")
				attr.colName = "\"${attr.colName}\""
			}
			def dupCheck = attrNames[attr.name]
			
			if (dupCheck) {
				dupCheck.value++
				attr.name += dupCheck.value
			} else {
				attrNames[attr.name] = 1
			}

			switch (attr.colTypeName) {
			case 'NUMERIC': case 'DECIMAL': 
				if (attr.scale == 0) {
					if (attr.scale == 0) {
						if (attr.size > 9) {
							attr.type =  "long"; break;
						} // else
						attr.type =  "int"; break;
					} 
				}    // else
				attr.type = 'big_decimal'; break;
			case 'TIMESTAMP': attr.type = "timestamp";   break;
			case 'SMALLINT':  attr.type = 'short';       break;
			case 'INTEGER':   attr.type = 'int';         break;
			case 'BIGINT':    attr.type = 'long';        break;
			case 'REAL':      attr.type = 'double';	     break;
			case 'CHAR':      attr.type = 'string';      break;
			case 'DATE': 
			case 'TIME':      attr.type = attr.colTypeName.toLowerCase(); break;
			default: println "ERROR: database column type ${attr.colTypeName} not handled"
			}
			attrs << attr
		} // while rs
	}
		
	String createDomainClass () {
		def domainStrWtr = new StringWriter()
		domainStrWtr <<= "class $domainName implements Serializable {\n"
		// domainStrWtr <<= "\tdef id\n" DO NOT PUT IN AN ID FOR 
		if (useRRNasKey) {
			domainStrWtr <<= "\tint id // relative record number\n"  
		} else if (index.size() == 1) { // DSL mapping, with  one key field
			def type = attrs.find {it.colName == index[0]}.type
			switch (type) {
			case 'string':		type = 'String';     break;
			case 'int':         type = 'int';        break;
			case 'big_decimal': type = 'BigDecimal'; break;
			case 'date':   	type = 'Date';       break; 
			case 'time':       type = 'Date';       break;
			case 'timestamp':   type = 'Date';       break;
			case 'double':     type = 'Double';     break;
			case 'short':       type = 'Short';      break;
			case 'long':        type = 'Long';       break;			
			default:
				println "ERROR: attribute type ${attr.type} not handled"
			}
			domainStrWtr <<= "\t${type} id\n"
		} 
		attrs.each { attr ->
			def type = '';
			switch (attr.type) {
			case 'string':      type = 'String';     break;
			case 'int':         type = 'int';        break;
			case 'big_decimal': type = 'BigDecimal'; break;
			case 'date':   		type = 'Date';       break; 
			case 'time':        type = 'Date';       break;
			case 'timestamp':   type = 'Date';       break;
			case 'double':      type = 'Double';     break;
			case 'short':       type = 'Short';      break;
			case 'long':        type = 'Long';       break;			
			default:
				println "ERROR: attribute type ${attr.type} not handled"
			}
			// gen attribute for RRN or composite keys   
			if (useRRNasKey || (index.size() > 1 || !index.find { it == attr.colName }) ) {
				domainStrWtr <<= "\t$type $attr.name\n"
			}
		}
		domainStrWtr <<= "\tstatic constraints = {\n"
		attrs.each { attr ->
			// don't add constraint for atomic key field    
			if (useRRNasKey || index.size() > 1 || !index.find { it == attr.colName } ) {// TODO: does each have a continue?
				def nines = ''
				domainStrWtr <<= "\t\t$attr.name("
				switch (attr.type) {
				case 'string':  
					domainStrWtr <<= "maxSize:${attr.size},"
					break
				case 'int': case 'short': case 'long':
					if (attr.colTypeName == 'NUMERIC' || attr.colTypeName == 'DECIMAL') {
						(attr.size).times {nines <<= '9'}
						domainStrWtr <<= "max:$nines,"
					}
					break
				case 'big_decimal': 
					(attr.size-attr.scale).times {nines <<= '9'}
					nines <<= "."
					attr.scale.times {nines <<= '9'}
					domainStrWtr <<= "max:new BigDecimal(\"$nines\"),"
					break
				default: // CONSIDER adding other constraints based on type
					if (verbose) println "INFO: no constraints added for field $attr.name of type $attr.type"
				}
				domainStrWtr <<= "nullable:false)\n"
			}
		}
		domainStrWtr <<= "\t}\n"
		// if not using RRN as key, add Mapping DSL 
		domainStrWtr <<= "\tstatic final boolean ASSIGNED_KEY = true\n"
		domainStrWtr <<= "\tstatic final boolean COMPOSITE_KEY  = ${(index.size() > 1)}\n"
		if (!useRRNasKey) { 
			domainStrWtr <<= buildMappingDSL()
			domainStrWtr <<= "\tboolean newEntity = false\n"
			if (index.size() == 1) { // one key field
				TableAttributes attr = attrs.find {it.colName == index[0] } 
				domainStrWtr <<= 
"""	static transients = ['${attr.name}', 'newEntity']
	def get${(attr.name[0].toUpperCase())}${(attr.name[1..(attr.name.size()-1)])}() {
		if (id) return id
		return ${(attr.type=='string'?'':0)}
	}
	void set${(attr.name[0].toUpperCase())}${(attr.name[1..(attr.name.size()-1)])} (def vlu) { id = vlu }
"""		
			} else {
				domainStrWtr <<= "\tstatic transients = ['newEntity']\n"
			}
		} // not useRRNasKey
			
		domainStrWtr <<= "}"

		new File("${domainName}.groovy").write(domainStrWtr.toString())
		if (verbose) println domainStrWtr.toString()	
		return  domainStrWtr.toString()
	}
	
	protected String buildCompositeId () {
		String compositeId = "\t\tid (composite:["
		index.each {key ->
			compositeId <<= "'${(attrs.find {it.colName == key }.name)}'"
		}
		compositeId = compositeId.toString().replaceAll(/''/,"','" )
		compositeId <<= '])\n'
		return compositeId
	}

	String buildMappingDSL() {
		def dslMap = new StringWriter()
		dslMap <<= "\tstatic mapping = {\n"
		dslMap <<= "\t\ttable ('$file')\n"
		dslMap <<= "\t\tversion (false)\n"
		dslMap <<= "\t\tid (generator:'assigned')\n"
		if (index.size() > 1) {
			dslMap <<= buildCompositeId()
		}
		dslMap <<= "\t\tcolumns {\n"

		if (index.size() > 1) {
			dslMap <<= "\t"+buildCompositeId()
		} else {
			def keyAttr = attrs.find {it.colName == index[0]}
			dslMap <<= "\t\t\tid (column:'${(index[0])}',type:'${keyAttr.type}')\n"
		}
		// add an entry for each column but for atomic keys
		attrs.each { attr ->
			// gen attribute for composite keys   
			if (index.size() > 1 || !index.find { it == attr.colName } ) {
				dslMap <<= "\t\t\t${attr.name} (column:'${attr.colName}', type:'${attr.type}')\n"
			} 
		}
		dslMap <<= "\t\t}\n" // close columns closure
		dslMap <<= "\t}\n"   // close static mappint closure
		return dslMap.toString()
	}  
		
		
	def retrieveIndex () { 
		def indexes = [:]
		boolean unique = true
		boolean approximate = false
		def rs = groovy.sql.Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver").
							getConnection().getMetaData().getIndexInfo(null, library, file, unique, approximate)
		while (rs.next()) { 
			String index = rs.getString('INDEX_NAME') // could have multiple rows with the same index
			String key = rs.getString('COLUMN_NAME')// where columns hold composite key
			def keys = indexes.get(index)
			if (keys) {         
				keys << key  // append column key value to the index
			} else {
				indexes[index] = [key] // put first column key value to index
			}
		}	
		if (indexes.size() == 0) {
			throw new RuntimeException("No unique index found for table, Click 'Use RRN as id/key' or create an i	index and retry")
		}
		if (verbose)  println "Found indexes: ${indexes}"
		def indexesByKeyCount = [:]
		indexes.each { indexesByKeyCount[it.key] = it.value.size()}
		def names = indexesByKeyCount.keySet().toList()
		names.sort {indexesByKeyCount[it]}
		if (verbose)  {
			println "Using index named ${(names[0])} with keys: ${(indexes[names[0]])}"
		}
		index = indexes[names[0]]
		index.eachWithIndex {keyElem, i ->
			if (keyElem.matches(/.*#.*/)) {
				index[i] = "\"${keyElem}\""
			}
		}
		return index
	}	
	String writeHibernateXML () {
		def xmlStrWtr = new StringWriter()
		def builder = new groovy.xml.MarkupBuilder(xmlStrWtr)
		def hbmXml = builder.'hibernate-mapping'() {
			builder.'class'(name:domainName, table:("${file}${RRN_SUFFIX}"))  {
				id(name:'id', type:'int') { generator('class':'assigned') }
				attrs.each { attr ->
					if (attr.type == 'string' ) {
						property(name:attr.name, column:attr.colName, type:attr.type, length:attr.size, 'not-null':attr.nullable)
					} else {
						property(name:attr.name, column:attr.colName, type:attr.type, precision:attr.size, scale:attr.scale, 'not-null':attr.nullable)
					}
				}  
				def questionMarkPlaceholders = ''
				attrs.size().times { questionMarkPlaceholders <<= "?, "	}
				questionMarkPlaceholders <<= "?" // one more for the rrn id that is ignored by the stored proc
				builder.'sql-insert'(callable:"true", check:"none", "{ call INSERT_$file ($questionMarkPlaceholders) }")  
			}
		}
		def xmlFile = new File("${domainName}.hbm.xml")
		def preamble = 
'''<?xml version=\"1.0\"?>
<!DOCTYPE hibernate-mapping PUBLIC \"-//Hibernate/Hibernate Mapping DTD 3.0//EN\"
\"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd\">
'''
		xmlFile.write(preamble+xmlStrWtr.toString())		
		if (verbose) println xmlStrWtr.toString()
		return xmlStrWtr.toString()
	}
	
	String createStoredProcedure () {
		def params = ''
		def values = ''
		attrs.each { attr ->
			params <<= "IN $attr.colName"
			switch (attr.colTypeName) {
			case 'NUMERIC': case 'DECIMAL': 
				if (attr.scale == 0) {
					if (attr.size > 9) {
						params <<= " long, "; break;
					} // else
					params <<= " int, "; break;
				} // else
				params <<= " decimal($attr.size,$attr.scale), "; break;
			case 'CHAR':  	
				params <<= " char($attr.size), "; break;
			case 'DATE': case 'TIME': case 'REAL': case 'SMALLINT': case 'INTEGER': case 'BIGINT': 	case 'TIMESTAMP': 
				params <<= " "+attr.colTypeName.toLowerCase() + ", "; break;
			default: println "ERROR: database column type ${attr.colTypeName} not handled"
			}

			values <<= "$attr.colName, "
		}
		params <<= 'IN rrnToIgnor int' // append placeholder for id value to be ignored by stored proc
		values = values.toString().replaceAll(/, $/, '') //	remove trailling commas
		def storProc =  
		"""CREATE PROCEDURE ${library}.INSERT_${file} ($params)
		LANGUAGE SQL 
		BEGIN
			INSERT INTO ${library}.$file ($values)
			VALUES($values);
		END"""
		storProc = storProc.toString().replaceAll(/\r/, ' ')
		def sql = groovy.sql.Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver")
		try {
			sql.execute storProc
		} catch (java.sql.SQLException e)	{
			println("WARN: ${e.toString()}. Dropping and recreating...")
			if (e.getErrorCode() == -454) {
				def sqlRtn = sql.getConnection().createStatement().executeUpdate("drop procedure ${library}.INSERT_${file} ")
				if (sqlRtn) println("ERROR: drop procedure return code: $sqlRtn")
				sql.getConnection().createStatement().executeUpdate(storProc)
			}
		}
		if (verbose) println storProc
		return storProc
	}
	
	String createRRNView () {
		def createView = "create view ${library}.${file}${RRN_SUFFIX} as select rrn(dbf) as id, dbf.* from ${library}.${file} dbf"
		def sql = groovy.sql.Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver")
		try {
			sql.getConnection().createStatement().executeUpdate createView
		} catch (java.sql.SQLException e)	{
			println("WARN: ${e.toString()}")	
			if (e.getErrorCode() == -601) {
				def sqlRtn = sql.getConnection().createStatement().executeUpdate("drop view ${library}.${file}${RRN_SUFFIX} ")
				if (sqlRtn) println("ERROR: drop view return code: $sqlRtn")
				sql.getConnection().createStatement().executeUpdate(createView)
			}
		}
		if (verbose) println createView 
		return createView
	}
	
	void writeHibernateConfigXML () {
		def hbmCfgXml = """<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="connection.transaction isolation">none</property>
		<!-- if you want to use the library list 
			remove grails-app/conf/DataSource.groovy url variable's library qualifier
			and uncomment this tag
        <property name="connection.libraries">DGD,QIWS,QGPL</property>
		-->
		<property name="show_sql">true</property>
		<!-- TODO put all filenames that are already in the deployment directory here 
			or put the new mapping resource in that file-->
        <mapping resource="${domainName}.hbm.xml"/>
    </session-factory>
</hibernate-configuration>
"""
		new File("hibernate.cfg.xml").write(hbmCfgXml)
	}
	
	private boolean isValidVariableName(String name) {
		static final def reservedWord = ['abstract':true, 'boolean':true, 'break':true, 'byte':true, 'case':true, 'catch':true, 'char':true, 'class':true, 'const':true, 'continue':true, 'default':true, 'do':true, 'double':true, 'else':true, 'extends':true, 'false':true, 'final':true, 'finally':true, 'float':true, 'for':true, 'goto':true, 'if':true, 'implements':true, 'import':true, 'instanceof':true, 'int':true, 'interface':true, 'long':true, 'native':true, 'new':true, 'null':true, 'package':true, 'private':true, 'protected':true, 'public':true, 'return':true, 'short':true, 'static':true, 'super':true, 'switch':true, 'synchronized':true, 'this':true, 'throw':true, 'throws':true, 'transient':true, 'true':true, 'try':true, 'void':true, 'volatile':true, 'while':true]
		return (name.matches('^[_A-Za-z][_A-Za-z0-9]*') && !reservedWord.getAt(name))
	}
	
}

